from django.db import models
from django.contrib.sites.models import Site

class PostManager(models.Manager):
    def get_query_set(self):
        return super(PostManager, self).get_query_set().filter(site=Site.objects.get_current())

class LivePostManager(PostManager):
    def get_query_set(self):
        extra_abuse_count = """
            SELECT COUNT(*) from popboard_abusereport
                WHERE popboard_post.id = popboard_abusereport.post_id
            """
        return super(LivePostManager, self).get_query_set().extra(
                    select={'abuse': extra_abuse_count}).exclude(censor=True).order_by('-created')

    def posts_for_thread(self, thread_id, user):
        qs = self.get_query_set().filter(thread__id=thread_id)
        if not user.is_staff:
            qs = qs.exclude(censor=True)
        return qs

class ThreadManager(models.Manager):
    """
    Model Manager For ``Thread`` which adds additional meta data
    """
    def get_query_set(self):
        '''
        This generates a QuerySet containing Threads and additional data used
        in generating a web page with a listing of discussions.
        Qset allows the caller to specify an initial
        queryset to work with.  If this is not set, all Threads will be
        returned.
        '''
        # number of posts in thread
        # censored threads don't count toward the total
        extra_post_count = """
            SELECT COUNT(*) from popboard_post
                WHERE popboard_post.thread_id = popboard_thread.id
                AND NOT popboard_post.censor
            """
        # figure out who started the discussion
        extra_starter = """
            SELECT username FROM auth_user
                WHERE auth_user.id = (SELECT user_id
                    from popboard_post WHERE popboard_post.thread_id = popboard_thread.id
                    ORDER BY popboard_post.created ASC
                    LIMIT 1)
            """
        extra_last_poster = """
            SELECT username FROM auth_user
                WHERE auth_user.id = (SELECT user_id
                    from popboard_post WHERE popboard_post.thread_id = popboard_thread.id
                    ORDER BY popboard_post.created DESC 
                    LIMIT 1)
            """

        return super(ThreadManager, self).get_query_set().filter(
                        site=Site.objects.get_current()).extra(
                        select={'post_count': extra_post_count,
                                'starter': extra_starter,
                                'last_poster': extra_last_poster}).order_by('-sticky', '-created')

    def get_favorites(self, user):
        wl = user.watchlist_set.all()
        return self.get_query_set().filter(pk__in=[x.id for x in wl])

    def get_category(self, cat_id):
        return self.get_query_set().filter(category__id=cat_id)

class LiveCategoryManager(models.Manager):
    """
    Model Manager for ``Category`` with additional meta data
    """
    def get_query_set(self):
        thread_count = """
            SELECT COUNT(*) from popboard_thread
            WHERE popboard_thread.category_id = popboard_category.id
            """
        post_count = """
            SELECT COUNT(*) from popboard_post LEFT JOIN 
            popboard_thread ON popboard_post.thread_id = popboard_thread.id 
            WHERE popboard_thread.category_id = popboard_category.id
            """
        return super(LiveCategoryManager, self).get_query_set().filter(site=Site.objects.get_current()).extra(
            select = {'thread_count': thread_count, 'post_count': post_count})
                
